import json
from django.http import HttpResponse
from django.db.models import Avg, Sum, Count, F, Q, Min, Max, When, Case
from django.db import connection
from .models import Student, Teacher, Course, Score


def index(request):
    # 1.查询平均成绩大于60分的同学的id和平均成绩；
    students = Student.objects.annotate(avg_score=Avg('score__number')).filter(avg_score__gte=60). \
        order_by('-avg_score').values('id', 'name', 'avg_score')
    # 2.查询所有同学的id、姓名、选课的数量、总成绩
    students = Student.objects.annotate(courses=Count('score__course'), total_score=Sum('score__number')). \
        order_by('-total_score', '-courses').values('id', 'name', 'courses', 'total_score')
    # 3.查询姓“李”的老师的个数
    teacher_count = Teacher.objects.filter(name__startswith='李').count()
    # 4.查询没学过“李老师”课的同学的id、姓名
    students = Student.objects.filter(~Q(score__course__teacher__name__startswith='李')).values('id', 'name').distinct()
    # 5.查询学过课程id为1和2的所有同学的id、姓名
    students = Student.objects.filter(score__course__in=[1, 2]).values('id', 'name').distinct()
    # 6.查询学过“黄老师”所教的“所有课”的同学的id、姓名
    students = Student.objects.filter(score__course__teacher__name='黄老师').values('id', 'name').distinct()
    # 7.查询所有课程成绩小于60分的同学的id和姓名
    students = Student.objects.filter(score__number__lt=60).values('id', 'name').distinct()
    # 8.查询没有学全所有课的同学的id、姓名
    course_count = Course.objects.values('name').distinct().count()
    students = Student.objects.annotate(course_count=Count('score__course__name')).filter(
        course_count__lt=course_count).values('id', 'name').order_by('course_count').distinct()
    # 9.查询所有学生的姓名、平均分，并且按照平均分从高到低排序
    students = Student.objects.annotate(avg_score=Avg('score__number')).values('id', 'name', 'avg_score').order_by(
        '-avg_score')
    # 10.查询各科成绩的最高和最低分，以如下形式显示：课程ID，课程名称，最高分，最低分；
    courses = Course.objects.annotate(max_score=Max('score__number'), min_score=Min('score__number')).values('id',
                                                                                                             'name',
                                                                                                             'max_score',
                                                                                                             'min_score')
    # 11.查询没门课程的平均成绩，按照平均成绩进行排序
    courses = Course.objects.annotate(avg_score=Avg('score__number')).values('id', 'name', 'avg_score').order_by(
        '-avg_score')
    # 12.统计总共有多少女生，多少男生
    students = Student.objects.aggregate(mans=Count('gender', filter=Q(gender=1)), womans=Count('gender', filter=Q(gender=2)))
    # 13.将“黄老师”的每一门课程都在原来的基础之上加5分
    """
    SELECT
        `score`.`number`,
        `course`.`name`,
        `teacher`.`name` 
    FROM
        `score`
        INNER JOIN `course` ON ( `score`.`course_id` = `course`.`id` )
        INNER JOIN `teacher` ON ( `course`.`teacher_id` = `teacher`.`id` ) 
    WHERE
        `teacher`.`name` = '黄老师' 
        LIMIT 21
    """
    # Score.objects.filter(course__teacher__name='黄老师').update(number=F('number') + 5)
    # 14.查询两门以上不及格的同学的id、姓名、以及不及格课程数
    students = Student.objects.annotate(course_count=Count('score__course__name', filter=Q(score__number__lt=60))).\
        filter(course_count__gte=2).values('id', 'name', 'course_count').order_by('course_count')
    # 15.查询每门课的选课人数
    courses = Course.objects.annotate(count=Count('score__student')).values('id', 'name', 'count').order_by('count')
    print(courses)
    if connection.queries:
        print(connection.queries[-1].get('sql'))
    return HttpResponse('ORM作业讲解')
